﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using Lg.Core.DbHelper;

namespace Lg.Core.Extensions
{
    public static class DBModelExtensions
    {
        /// <summary>
        /// 插入实体所有属性字段(排除值为null和自增特性的属性)
        /// </summary>
        /// <param name="model"></param>
        /// <param name="dBHelper"></param>
        /// <returns></returns>
        public static int Insert(this IDBModel model,IDBHelper? dBHelper=null,bool isBackIndex=false)
        {
            //判断是否传入IDBHelper实例，没有则去实例化
            if (dBHelper == null)
            {
                dBHelper = new SqlHelper();
            }
            //反射获取model的属性集合(排除值为null和自增特性的属性)
            Type _type = model.GetType();
            PropertyInfo[] _propertyInfos = _type.GetProperties().Where(p =>p.GetValue(model) != null && p.CustomAttributes.FirstOrDefault(a => a.AttributeType.Name.Equals("GrowAttribute"))==null).ToArray();
            if(_propertyInfos.Length > 0)
            {
                string sql = $"Insert into [{_type.Name}] ({string.Join(",", _propertyInfos.Select(p => p.Name))}) values({string.Join(",", _propertyInfos.Select(p => "@" + p.Name))})";
                List<DBParameter> listParam = _propertyInfos.Select(p => new DBParameter(p.Name, p.GetValue(model)!)).ToList();
                if (isBackIndex)
                {
                    sql += " Select SCOPE_IDENTITY()";
                    return Convert.ToInt32(dBHelper.ExecuteScalar(sql, listParam));
                }
                return dBHelper.ExecuteNonQuery(sql, listParam);
            }
            else
            {
                throw new Exception("未提供有效的可插入字段！");
            }
        }
        /// <summary>
        /// 插入指定字段参数的实体(排除值为null和自增特性的属性)
        /// 字段参数处理方式有两种include(包含)/exclude(排除)
        /// </summary>
        /// <param name="model">继承IDBModel的数据库实体类型</param>
        /// <param name="dbHelper"></param>
        /// <param name="option"></param>
        /// <param name="fields"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static int Insert(this IDBModel model, IDBHelper? dbHelper,bool isBackIndex, FieldOption option, params string[] fields)
        {
            if (dbHelper == null)
            {
                dbHelper = new SqlHelper();
            }
            Type _type = model.GetType();
            PropertyInfo[] _propertyInfos = _type.GetProperties();
            PropertyInfo[] _usePropertyInfos;
            if (option == FieldOption.Include)
            {
                _usePropertyInfos = _propertyInfos.Where(p => fields.Contains(p.Name) && p.GetValue(model) != null && !p.CustomAttributes.Where(a => a.AttributeType.Name.Equals("GrowAttribute")).Any()).ToArray();
            }
            else
            {
                _usePropertyInfos = _propertyInfos.Where(p => !fields.Contains(p.Name) && p.GetValue(model) != null && !p.CustomAttributes.Where(a => a.AttributeType.Name.Equals("GrowAttribute")).Any()).ToArray();
            }
            if (_usePropertyInfos.Length > 0)
            {
                string sql = $"Insert into [{_type.Name}] ({string.Join(",", _usePropertyInfos.Select(p => p.Name))}) values({string.Join(",", _usePropertyInfos.Select(p => "@" + p.Name))})";
                List<DBParameter> listParam = _usePropertyInfos.Select(p => new DBParameter(p.Name, p.GetValue(model)!)).ToList();
                if (isBackIndex)
                {
                    sql += " Select SCOPE_IDENTITY()";
                    return Convert.ToInt32(dbHelper.ExecuteScalar(sql, listParam));
                }
                return dbHelper.ExecuteNonQuery(sql, listParam);
            }
            else
            {
                throw new Exception("未提供有效的可插入字段！");
            }

        }

        /// <summary>
        /// 实体映射方式更新单行数据
        /// 不指定字段参数(排除值为null和自增特性的属性)
        /// 实体内必须包含特性为 Identity的属性
        /// </summary>
        /// <param name="model"></param>
        /// <param name="dBHelper"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static int Edit(this IDBModel model, IDBHelper? dBHelper=null)
        {
            if (dBHelper == null)
            {
                dBHelper = new SqlHelper(new JsonHelper());
            }
            Type _type = model.GetType();
            PropertyInfo[] _propertyInfos = _type.GetProperties();
            PropertyInfo[] _usePropertyInfos=_propertyInfos.Where(p => p.GetValue(model) != null && !p.CustomAttributes.Where(a => a.AttributeType.Name.Equals("GrowAttribute")).Any()).ToArray(); ;
            PropertyInfo? _identityPropertyInfo = _propertyInfos.FirstOrDefault(p => p.GetValue(model) != null && p.CustomAttributes.Where(a => a.AttributeType.Name.Equals("IdentifyAttribute")).Any());
            if (_identityPropertyInfo != null)
            {
                if (_usePropertyInfos.Length > 0)
                {
                    string sql = $"update  [{_type.Name}] set {string.Join(",", _usePropertyInfos.Select(p => $"[{p.Name}]=@{p.Name}"))} where {_identityPropertyInfo.Name}=@{_identityPropertyInfo.Name}";
                    List<DBParameter> listParam = _usePropertyInfos.Select(p => new DBParameter(p.Name, p.GetValue(model) ?? DBNull.Value)).ToList();
                    if (!listParam.Any(p => p.ParameterName == _identityPropertyInfo.Name))
                    {
                        listParam.Add(new DBParameter(_identityPropertyInfo.Name, _identityPropertyInfo.GetValue(model)!));
                    }
                    return dBHelper.ExecuteNonQuery(sql, listParam);
                }
                else
                {
                    throw new Exception("未提供有效的可更新字段！");
                }

            }
            else
            {
                throw new Exception($"未找到类型:[{_type.Name}]中包含[Identity]特性且值不为null的属性，无法构造where条件");
            }
        }
        /// <summary>
        /// 实体映射方式更新单行数据
        /// 指定字段参数(排除值为null和自增特性的属性)
        /// 字段参数处理方式有两种include(包含)/exclude(排除)
        /// 实体内必须包含特性为 Identity的属性 
        /// </summary>
        /// <param name="model"></param>
        /// <param name="dBHelper"></param>
        /// <param name="option"></param>
        /// <param name="fields"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>

        public static int Edit(this IDBModel model, IDBHelper? dBHelper, FieldOption option, params string[] fields)
        {
            if (dBHelper == null)
            {
                dBHelper = new SqlHelper(new JsonHelper());
            }
            Type _type = model.GetType();
            PropertyInfo[] _propertyInfos = _type.GetProperties();//原始属性集合
            PropertyInfo[] _usePropertyInfos;//最终符合条件的更新属性集合
            PropertyInfo? _identityPropertyInfo = _propertyInfos.FirstOrDefault(p => p.GetValue(model) != null && p.CustomAttributes.Where(a => a.AttributeType.Name.Equals("IdentifyAttribute")).Any());
            if (_identityPropertyInfo != null)
            {
                if (option == FieldOption.Include)
                {
                    _usePropertyInfos = _propertyInfos.Where(p => fields.Contains(p.Name) && p.GetValue(model) != null && !p.CustomAttributes.Where(a => a.AttributeType.Name.Equals("GrowAttribute")).Any()).ToArray();
                }
                else
                {
                    _usePropertyInfos = _propertyInfos.Where(p => !fields.Contains(p.Name) && p.GetValue(model) != null && !p.CustomAttributes.Where(a => a.AttributeType.Name.Equals("GrowAttribute")).Any()).ToArray();
                }
                if (_usePropertyInfos.Length > 0)
                {
                    string sql = $"update  [{_type.Name}] set {string.Join(",", _usePropertyInfos.Select(p => $"[{p.Name}]=@{p.Name}"))} where {_identityPropertyInfo.Name}=@{_identityPropertyInfo.Name}";
                    List<DBParameter> listParam = _usePropertyInfos.Select(p => new DBParameter(p.Name, p.GetValue(model) ?? DBNull.Value)).ToList();
                    if (!listParam.Any(p => p.ParameterName == _identityPropertyInfo.Name))
                    {
                        listParam.Add(new DBParameter(_identityPropertyInfo.Name, _identityPropertyInfo.GetValue(model)!));
                    }
                    return dBHelper.ExecuteNonQuery(sql, listParam);
                }
                else
                {
                    throw new Exception("未提供有效的可更新字段！");
                }

            }
            else
            {
                throw new Exception($"未找到类型:[{_type.Name}]中包含[Identity]特性且值不为null的属性，无法构造where条件");
            }

        }

    }
}
